var mysql = require('./mysql/mysql');
var utils = require('../../shared/utils');
var userDao = module.exports;

/**
 * create a new user
 */
userDao.createUser = function (uid, referee1,regChannel,cb) {

    var sql = 'insert into user (uid,regChannel,referee1,regDate) values (?, ?, ?, ?)';
    var args = [uid,regChannel,referee1,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("userDao.createUser: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

/**
 * Get user info by phoneNo
 * @param {String} phoneNo
 * @param {function} cb
 */
userDao.getUserById = function (uid,referee,regChannel,cb){

    var sql = 'SELECT sid,coin,role,regChannel,referee1,referee2 FROM user WHERE uid = ?';
    var args = [uid];
    //console.log(' getUserById:%s,referee:%s,regChannel:%s',uid,referee,regChannel);
    mysql.query(sql, args, function(err, user){
        if(err) {
            console.log("userDao.getUserById: ", sql, args, err, user);
            cb(1, null);
            return;
        }
        if(!user.length && uid && uid != null) {               //不存在则插入,默认官渠
            if(!regChannel) regChannel = 'official';
            userDao.createUser(uid,referee,regChannel, function (err,user) {
                if(err){
                    cb(1,null);
                    console.log(' create user:%s,uid:%s',JSON.stringify(user),uid);
                    return;
                }
                cb(null,{sid:user.insertId,coin:0,role:0});
            });
        }else {
            cb(null,user[0]);
        }
    });
};

userDao.getRefereeNum = function (uid,cb) {
    var sql1 = 'SELECT COUNT(sid) ref1Num FROM USER WHERE referee1 = ?';
    var sql2 = 'SELECT COUNT(sid) ref2Num FROM USER WHERE referee2 = ?';
    mysql.query(sql1, [uid], function(err, ref1Num){
        if(err){
            cb(1,null);
        } else {
            mysql.query(sql2, [uid], function(err, ref2Num){
                if(err){
                    cb(null,{
                        ref1Num:ref1Num[0].ref1Num,
                        ref2:0
                    });
                }else {
                    cb(null,{
                        ref1Num:ref1Num[0].ref1Num,
                        ref2Num:ref2Num[0].ref2Num
                    });
                }
            });
        }
    });
};

/**
 * 查找uid推荐的玩家
 * @param uid
 * @param cb
 */
userDao.getReferee1Info = function (uid,cb) {
    var sql = 'SELECT sid,regDate FROM user WHERE referee1 = ?';
    mysql.query(sql, [uid], function(err, ref1){
       if(err){
           cb(1,null);
       } else {
           cb(null,ref1);
       }
    });
};

userDao.getReferee2Info = function (uid,cb) {
    var sql = 'SELECT sid,regDate FROM user WHERE referee2 = ?';
    mysql.query(sql, [uid], function(err, ref2){
        if(err){
            cb(1,null);
        } else {
            cb(null,ref2);
        }
    });
};

userDao.getCashList = function (uid,cb) {
    var sql = 'SELECT rmb,addDate FROM cash_list WHERE uid = ?';
    mysql.query(sql, [uid], function(err, list){
        if(err){
            cb(1,null);
        } else {
            cb(null,list);
        }
    });
};

/**
 * 获取玩家充值的金币,已经上一级已经获得了多少佣金
 * @param uid
 * @param coin
 */
userDao.issueCommission = function (uid,referee,cb) {
    var sql1 ='SELECT SUM(total_fee/100) AS total_fee FROM orders WHERE uid = ?;';
    mysql.query(sql1,[uid], function (err,order) {
        if(err){
            cb(1,null);
        }else {
            var sq12 ='SELECT SUM(coin) coin FROM commission WHERE uid = ? AND contributor = ?;';
            mysql.query(sq12,[referee,uid], function (err,comi) {
                if(err){
                    cb(1,null);
                }else {
                    cb(null,{totalFee:order[0].total_fee,totalComi:comi[0].coin})
                }
            })
        }
    })
};

/**
 * 获取佣金记录
 * @param uid
 * @param cb
 */
userDao.getPostList = function (uid,cb) {
    var sql = 'SELECT coin,status,addDate FROM commission WHERE uid = ? ORDER BY `addDate` DESC';
    mysql.query(sql, [uid], function(err, list){
        if(err){
            cb(1,null);
        } else {
            cb(null,list);
        }
    });
};
/**
 * 提取未提现佣金
 * @param uid
 * @param cb
 */
userDao.extractCoin = function (uid,cb) {
    var sql1 = 'SELECT SUM(coin) AS totalCoin FROM commission WHERE `status` = 0 AND uid = ?';
    var sql2 = 'UPDATE commission SET `status` = 1 WHERE uid = ?';
    mysql.query(sql1, [uid], function(err, res){
        if(err){
            cb(1,null);
            console.log('extractCoin sql1 error:',err,sql1);
        } else {
            mysql.query(sql2,[uid], function (err) {
                if(err){
                    cb(1,null);
                    console.log('extractCoin sql2 error:',err,sql2);
                }else {
                    var totalCoin = res[0].totalCoin ? res[0].totalCoin:0;
                    cb(null,totalCoin);
                }
            })
        }
    });
};

/**
 * 插入佣金记录
 * @param uid
 * @param cb
 */
userDao.addCommission = function (uid,coin,contributor) {
    var self = this;
    self.getUserById(uid,null,null, function (err,user,contributor) {
        if(user){
            var sql = 'INSERT INTO commission (sid,uid,coin,contributor,`addDate`) VALUES (?,?,?,?,?)';
            var args = [user.sid,uid,coin,contributor,utils.toMysqlFormat(new Date())];
            mysql.query(sql, args, function(err){
                if(err){
                    console.log('addCommission error',err,args);
                }
            });
        }
    });
};

/**
 *
 * @param uid
 * @param referee1
 * @param referee2
 */
userDao.updateUserRef2 = function (uid,ref2) {
    var sql = 'update user set referee2 = ? where uid = ?';
    var args = [ref2,uid];
    mysql.update(sql,args, function(err,res){

        if(err){
            console.log('updateUserRef2 err:%s sql:%s,data:%s,res:%s',err,sql,JSON.stringify(args),JSON.stringify(res));
        }
    });
};

userDao.increaseUserCoin = function (uid,coin,cb) {
    var sql = 'update user set coin = coin+? where uid = ?';
    mysql.query(sql, [coin,uid], function(err){
        if(err){
            console.log('updateUserCoin err:%s,data:%s',err,JSON.stringify([coin,uid]));
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

userDao.recorderCash = function (sid,uid,mch_billno,coin,rmb) {
    var sql = 'INSERT INTO cash_list (sid,uid,mch_billno,coin,rmb,`addDate`) VALUES (?,?,?,?,?,?)';
    mysql.query(sql, [sid,uid,mch_billno,coin,rmb,utils.toMysqlFormat(new Date())], function(err){
        if(err) {
            console.log('recorderCash err:%s,data:%s', err, JSON.stringify([coin, uid,sid]));
        }
    });
};
userDao.recorderOrders = function (sid,uid,total_fee,orderid,wxorderid,payDate,cb) {
    var sql = 'INSERT INTO orders (sid,uid,total_fee,orderid,wxorderid,payDate) VALUES (?,?,?,?,?,?)';
    mysql.query(sql, [sid,uid,total_fee,orderid,wxorderid,payDate], function(err){
        if(err) {
            console.log('recorderOrders err:%s,data:%s', err);
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};

/**
 *
 * @param uid
 * @param flag  which wheel
 * @param item  which item
 */
userDao.recordUserAction = function (uid,sid,flag,item,coin) {
    var sql = 'insert into user_action (sid,uid,flag,item,coin,addDate) values(?,?,?,?,?,?)';
    var params = [sid,uid,flag,item,coin,utils.toMysqlFormat(new Date())];
    mysql.query(sql,params,function (err) {
        if(err){
            console.log('recordUserAction error:%s ,params:%s',err,params);
        }
    });
};

userDao.userAction = function (uid,flag,cb) {
    var sql = 'select * from user_action where uid = ? and flag = ?';
    mysql.query(sql,[uid,flag],function (err,list) {
        if(err){
            console.log('recordUserAction error:%s ,params:%s',err,[uid,flag]);
            cb(1,null);
        }else {
            cb(null,parseInt(list.length));
        }
    })
};

/********管理员后台*********/

userDao.getAllUser = function (cb) {
    var sql = 'SELECT * FROM `user` ORDER BY `coin` DESC;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser error:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};

userDao.getUserOrders = function (cb) {
    var sql = 'SELECT * FROM `orders` ORDER BY `payDate` DESC ;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser getUserOrders:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};
userDao.getPostCash = function (cb) {
    var sql = 'SELECT * FROM `cash_list` ORDER BY `addDate` DESC ;';
    mysql.query(sql,null,function (err,list) {
        if(err){
            console.log('getAllUser getPostCash:%s ,list:%s',err,list);
            cb(1,null);
        }else {
            cb(null,list);
        }
    });
};

/********渠道管理*********/

userDao.getChannelWorth = function (channel,cb) {
    var sql1 = 'SELECT SUM(coin) AS totalCoin  FROM `user` WHERE regChannel= ?;';
    var sql2 = 'SELECT SUM(O.total_fee) AS total_fee FROM `user` U,(SELECT `uid`,SUM(total_fee) AS total_fee FROM `orders` WHERE `status` = 0 AND DATE_FORMAT(payDate,"%Y-%m-%d") < CURDATE() GROUP BY `uid` ) AS O WHERE U.uid = O.uid AND U.`regChannel` = ? ;';
    var sql3 = 'SELECT SUM(C.rmb) AS rmb FROM `user` U,(SELECT `uid`,SUM(rmb) AS rmb FROM `cash_list` WHERE `status` = 0 AND DATE_FORMAT(addDate,"%Y-%m-%d") < CURDATE() GROUP BY `uid` ) AS C WHERE U.uid = C.uid AND U.`regChannel` = ?;';
    mysql.query(sql1,[channel],function (err,res1) {
        if (err) {
            console.log('getChannelWorth1 err:%s ,res1:%s', err, res1);
            cb(1, null);
        } else {
            mysql.query(sql2, [channel], function (err, res2) {
                if (err) {
                    console.log('getChannelWorth2 err:%s ,res1:%s', err, res2);
                    cb(1, null);
                } else {
                    mysql.query(sql3, [channel], function (err, res3) {
                        if (err) {
                            console.log('getChannelWorth3 err:%s ,res1:%s', err, res3);
                            cb(1, null);
                        } else {
                            cb(err,{totalCoin:res1[0].totalCoin,totalFee:res2[0].total_fee,totalRmb:res3[0].rmb});
                        }
                    });
                }
            });
        }
    });
};

userDao.getChannelUser = function (channel,cb) {
    var sql = 'SELECT * FROM `user` where regChannel= ? ORDER BY `coin` DESC ;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelUser err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};

userDao.getChannelOrders = function (channel,cb) {
    var sql = 'SELECT U.sid,U.uid,U.regChannel,U.coin,O.total_fee FROM `user` U,(SELECT `uid`,SUM(total_fee) as total_fee from `orders` where `status`= 0 AND DATE_FORMAT(payDate,"%Y-%m-%d") < CURDATE() GROUP BY `uid` ) as O where U.uid = O.uid AND U.`regChannel` = ? ORDER BY U.`coin` DESC;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelOrders err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};

userDao.listChannel = function (uid,channel,coinNum,coinValue,orderNum,cashNum,dividedRmb,cb) {
    var sql = 'INSERT INTO channel_list (uid,channel,coinNum,coinValue,orderNum,cashNum,dividedRmb,`addDate`) VALUES (?,?,?,?,?,?,?,?)';
    var args = [uid,channel,coinNum,coinValue,orderNum,cashNum,dividedRmb,utils.toMysqlFormat(new Date())];
    mysql.query(sql,args, function (err) {
        if(err){
            console.log('listChannel err:%s',err);
            cb(1,null);
        }else {
            cb(null,1);
        }
    });
};
userDao.channelHistory = function (channel,cb) {
    var sql = 'SELECT * FROM channel_list WHERE channel =? ORDER BY `addDate` DESC;';

    mysql.query(sql,[channel], function (err,res) {
        if(err){
            console.log('listChannel err:%s',err);
            cb(1,null);
        }else {
            cb(null,res);
        }
    });
};

userDao.getChannelCash = function (channel,cb) {
    var sql = 'SELECT U.sid,U.uid,U.regChannel,U.coin,C.rmb FROM `user` U,(SELECT `uid`,SUM(rmb) as rmb from `cash_list` where `status`= 0 AND DATE_FORMAT(addDate,"%Y-%m-%d") < CURDATE()  GROUP BY `uid` ) as C where U.uid = C.uid AND U.`regChannel` = ? ORDER BY U.`coin` DESC ;';
    mysql.query(sql,[channel],function (err,lists) {
        if(err){
            console.log('getChannelCash err:%s ,users:%s',err,lists);
            cb(1,null);
        }else {
            cb(null,lists);
        }
    });
};


userDao.balanceChannel = function (channel,openid,cb) {
    var sql1 = 'UPDATE orders O,USER U SET O.status=1 WHERE O.uid=U.uid AND U.regChannel= ? AND DATE_FORMAT(O.payDate,"%Y-%m-%d") < CURDATE();';
    var sql2 = 'UPDATE cash_list C,USER U SET C.status=1 WHERE C.uid=U.uid AND U.regChannel= ? AND DATE_FORMAT(C.addDate,"%Y-%m-%d") < CURDATE();';
    var orderChange =0,cashChange = 0;
    mysql.query(sql1,[channel],function (err,res1) {
        if(err){
            console.log('balanceChannel1 err:%s ,users:%s',err,JSON.stringify(res1));
            cb(1,null);
        }else {
            orderChange = res1.changedRows;
            mysql.query(sql2,[channel],function (err,res2) {
                if(err){
                    console.log('balanceChannel2 err:%s ,users:%s',err,JSON.stringify(res2));
                    cb(1,null);
                    return;
                }
                cashChange = res2.changedRows;
                if(orderChange == 0 && 0 == cashChange ){
                    cb(null,{cashChange:cashChange,orderChange:orderChange});
                }else {
                    var sql3 = 'INSERT INTO channel_balance (uid,channel,cashNum,orderNum,`addDate`) VALUES (?,?,?,?,?)';
                    var args = [openid,channel,cashChange,orderChange,utils.toMysqlFormat(new Date())];
                    mysql.query(sql3,args, function (err) {
                        if(err){
                            console.log('addChannelBan err:%s',err);
                            cb(1,null);
                        }else {
                            cb(null,{cashChange:cashChange,orderChange:orderChange});
                        }
                    });
                }

            });

        }
    });
};

/********插入测试数据*********/
userDao.createUserR1R2 = function (uid, referee1,ref2,cb) {

    var sql = 'insert into user (uid,referee1,referee2,regDate) values (?,?,?,?)';
    var args = [uid, referee1,ref2,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("userDao.createUserR1R2: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

userDao.createCom = function (uid,oid,coin,cb) {

    var sql = 'insert into cash_list (uid,mch_billno,coin,rmb,addDate) values (?,?,?,?,?)';
    var args = [uid, oid,coin,coin,utils.toMysqlFormat(new Date())];

    mysql.insert(sql, args, function(err, res) {

        if (err) {
            console.log("userDao.createCom: ", sql, args, err, res);
            cb(1, null);
            return;
        }
        else {
            cb(null, res);
        }
    });
};

